﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Excel = Microsoft.Office.Interop.Excel;
using System.Reflection;
using System.Data;
using System.Data.OleDb;
using SmallSoft.Language.Model.Interface;
using SmallSoft.Language.Model.Factory;

namespace SmallSoft.Language.Database
{
    public class ExcelDatabase
    {
        private String fileName;
        public ExcelDatabase()
        {
            fileName = Environment.CurrentDirectory + "\\DictDatabase.xlsx";
        }
        public List<IWordModel> GetData()
        {
            List<IWordModel> result = new List<IWordModel>();
            try
            {                                                                
                DataSet dict = OpenFile();
                foreach (DataRow row in dict.Tables[0].Rows)
                {
                    IWordModel word = WordModelFactory.GetWordModel();
                    word.Word = row[0].ToString().Trim();
                    word.WordMean = row[1].ToString().Trim();

                    result.Add(word);
                }
            }
            catch (System.Exception ex)
            {
                throw ex;
            }                
            return result;
        }
        private DataSet OpenFile()
        {

            string[] fileParts = fileName.Split('.');

            string connString = "";
            if (fileName.Length > 1 && fileParts[1] == "xls")// sử dụng cho Microsoft Excel 2003
            {
                connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileName + ";Extended Properties=Excel 8.0";
            }
            else // sử dụng cho Microsoft Excel 2007
            {
                connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";Extended Properties=Excel 12.0";
            }            
            OleDbConnection oledbConn = new OleDbConnection(connString);
            DataSet ds = new DataSet();
            try
            {
                oledbConn.Open();
                OleDbCommand cmd = new OleDbCommand("SELECT * FROM [Sheet1$]", oledbConn);
                OleDbDataAdapter oleda = new OleDbDataAdapter();
                oleda.SelectCommand = cmd;                
                oleda.Fill(ds, "Table");
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                oledbConn.Close();
            }
            return ds;
        }
    }
}
